// Eric Battalio
// http://blogs.msdn.com/ebattalio
// BizTalk Server 2006
// 
// Modified by Ignacio Quijas 
// Action: Recompiled for BizTalk 2010 Compatibility
// Last Modification Date: February 2011
//
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace BizTalk.MapperExtensions.DBFunktoids
{
    /// <summary>
    /// Database result source type. This could be expanded to
    /// include specialized collections containing relevant data,
    /// etc. 
    /// </summary>
    internal enum DBResultSourceTypes
    {
        /// <summary>
        /// Source is a DataSet
        /// </summary>
        DataSet 
    }

    internal class DBResult
    {
        /// <summary>
        /// Database result source type (defaults to DataSet)
        /// </summary>
        public DBResultSourceTypes SourceType = DBResultSourceTypes.DataSet;
        /// <summary>
        /// Holds the DataSet result from query
        /// </summary>
        public DataSet DataSet;
        /// <summary>
        /// Error message relating to the db connection or sp execution attempt 
        /// </summary>
        public string ErrorMessage;
        /// <summary>
        /// True if the instance is in error (and further ops should be ignored)
        /// </summary>
        public bool Error;
    }

    public class DBFunk
    {
        // Want each thread to have its own facts
        // DON'T INITIALIZE THIS HERE OR IT WILL FAIL INTERMITTENTLY!
        [ThreadStatic]  
        private static Dictionary<string, DBResult> _facts;

        /// <summary>
        /// Runs a stored procedure and captures the output for later reference by
        /// the Error and Value Extractor functoids.
        /// </summary>
        /// <param name="connectionString">Database connection string</param>
        /// <param name="storedProcedureName">Name of stored procedure to run</param>
        /// <param name="parameterNames">Stored procedure parameter names, | (pipe) delimited</param>
        /// <param name="parameters">Paramter values; missing parameters are assigned null</param>
        /// <returns>Guid representing this query instance</returns>
        private string RunStoredProcedureEx(string connectionString, string storedProcedureName, string parameterNames, params string[] parameters)
        {
            // Make sure we have an instance
            if(_facts == null)
                _facts = new Dictionary<string,DBResult>();

            string guid = Guid.NewGuid().ToString();
            DBResult procResult = new DBResult();

            // Need a sqlConnection
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    // Need a sqlCommand
                    using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
                    {
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlCommand.CommandText = storedProcedureName;
                        // If stored proc parameters were given
                        if (parameters.Length > 0)
                        {
                            // Names must be split by |
                            string[] splitParameterNames = parameterNames.Split('|');
                            // If there is at least one parameter name (since at least
                            // one proc value was passed)
                            if (splitParameterNames.Length > 0)
                            {
                                // First build up the list of parameters
                                foreach (string parameterName in splitParameterNames)
                                {
                                    // Parameter value defaults to Null
                                    SqlParameter p = new SqlParameter(parameterName, DBNull.Value);
                                    sqlCommand.Parameters.Add((SqlParameter)p);
                                }
                                // Now assign the parameter values
                                for (int i = 0; i < parameters.Length; i++)
                                    sqlCommand.Parameters[i].Value = parameters[i];
                            }
                            else
                            {
                                // Must supply a param name if a param value is passed
                                procResult.Error = true;
                                procResult.ErrorMessage = "No parameter names given for parameters";
                            }
                        }
                        // If everything is happy so far
                        if (procResult.Error == false)
                        {
                            // Get ducks in row to execute stored proc into DataSet
                            SqlDataAdapter sqlAdapter = new SqlDataAdapter();
                            sqlAdapter.SelectCommand = sqlCommand;
                            DataSet dsResult = new DataSet();
                            // Could set a timeout on the sqlCommand
                            sqlAdapter.Fill(dsResult);
                            // If query pulled back at least one row
                            if(dsResult.Tables.Count > 0 && dsResult.Tables[0].Rows.Count > 0)
                                procResult.DataSet = dsResult;
                            else
                            {
                                // No data
                                procResult.Error = true;
                                procResult.ErrorMessage = "No data";
                            }
                        }
                    }
                }
                catch (SqlException sqle)
                {
                    // Something Bad happened, go ahead and remember it
                    procResult.Error = true;
                    procResult.ErrorMessage = sqle.ToString();
                }
            }
            _facts.Add(guid, procResult);
            // The guid will be used as a key by the extractor funcs
            return guid;
        }

        public string RunStoredProcedure(string connectionString, string storedProcedureName)
        {
            return RunStoredProcedureEx(connectionString, storedProcedureName, null);
        }

        public string RunStoredProcedure(string connectionString, string storedProcedureName, string paramNames, string p1)
        {
            return RunStoredProcedureEx(connectionString, storedProcedureName, paramNames, p1);
        }

        public string RunStoredProcedure(string connectionString, string storedProcedureName, string paramNames, string p1, string p2)
        {
            return RunStoredProcedureEx(connectionString, storedProcedureName, paramNames, p1, p2);
        }

        public string RunStoredProcedure(string connectionString, string storedProcedureName, string paramNames, string p1, string p2, string p3)
        {
            return RunStoredProcedureEx(connectionString, storedProcedureName, paramNames, p1, p2, p3);
        }

        public string RunStoredProcedure(string connectionString, string storedProcedureName, string paramNames, string p1, string p2, string p3, string p4)
        {
            return RunStoredProcedureEx(connectionString, storedProcedureName, paramNames, p1, p2, p3, p4);
        }

        public string RunStoredProcedure(string connectionString, string storedProcedureName, string paramNames, string p1, string p2, string p3, string p4, string p5)
        {
            return RunStoredProcedureEx(connectionString, storedProcedureName, paramNames, p1, p2, p3, p4, p5);
        }

        public string RunStoredProcedure(string connectionString, string storedProcedureName, string paramNames, string p1, string p2, string p3, string p4, string p5, string p6)
        {
            return RunStoredProcedureEx(connectionString, storedProcedureName, paramNames, p1, p2, p3, p4, p5, p6);
        }

        public string RunStoredProcedure(string connectionString, string storedProcedureName, string paramNames, string p1, string p2, string p3, string p4, string p5, string p6, string p7)
        {
            return RunStoredProcedureEx(connectionString, storedProcedureName, paramNames, p1, p2, p3, p4, p5, p6, p7);
        }

        public string RunStoredProcedure(string connectionString, string storedProcedureName, string paramNames, string p1, string p2, string p3, string p4, string p5, string p6, string p7, string p8)
        {
            return RunStoredProcedureEx(connectionString, storedProcedureName, paramNames, p1, p2, p3, p4, p5, p6, p7, p8);
        }

        public string RunStoredProcedure(string connectionString, string storedProcedureName, string paramNames, string p1, string p2, string p3, string p4, string p5, string p6, string p7, string p8, string p9)
        {
            return RunStoredProcedureEx(connectionString, storedProcedureName, paramNames, p1, p2, p3, p4, p5, p6, p7, p8, p9);
        }

        public string RunStoredProcedure(string connectionString, string storedProcedureName, string paramNames, string p1, string p2, string p3, string p4, string p5, string p6, string p7, string p8, string p9, string p10)
        {
            return RunStoredProcedureEx(connectionString, storedProcedureName, paramNames, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10);
        }

        /// <summary>
        /// Returns a value for a data column
        /// </summary>
        /// <param name="guidFromSource">Guid from data source (eg, stored proc runner)</param>
        /// <param name="columnName">Column name of data value to return</param>
        /// <returns>Data value or string.Empty if not found</returns>
        public string ExtractValue(string guidFromSource, string columnName)
        {
            // Assume the column won't be found
            string returnValue = string.Empty;
            // If we know about this guid
            if (_facts.ContainsKey(guidFromSource))
            {
                DBResult procResult = _facts[guidFromSource];
                // If the result instance isn't in an error state
                if (!procResult.Error)
                {
                    // Note that the query function ensures there is 
                    // at least one table and one row of data
                    DataTable dtResult = procResult.DataSet.Tables[0];
                    int columnIndex = dtResult.Columns.IndexOf(columnName);
                    // If the column name exists in the results
                    if (columnIndex >= 0)
                        returnValue = dtResult.Rows[0].ItemArray[columnIndex].ToString();
                }
            }
            return returnValue;
        }

        /// <summary>
        /// Returns the error message for the source if it has an error
        /// </summary>
        /// <param name="guidFromSource">Guid from data source (eg, stored proc runner)</param>
        /// <returns>Error message or string.Empty if no error or source cannot be found</returns>
        public string ExtractError(string guidFromSource)
        {
            // If we know this guid and it points to a result that has an error, return it
            if (_facts.ContainsKey(guidFromSource) && _facts[guidFromSource].Error == true)
                return _facts[guidFromSource].ErrorMessage;
            // Either couldn't find guid or found it and no error
            return string.Empty;
        }
    }
}
